/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package DATA;

/**
 *
 * @author Administrator
 */
/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */



import com.mysql.jdbc.Connection;
import com.mysql.jdbc.Statement;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;



public class accesolog {

        Connection connection = null;

    public accesolog () throws SQLException{
        try {
        // Load the JDBC driver
        String driverName = "org.gjt.mm.mysql.Driver"; // MySQL MM JDBC driver
        Class.forName(driverName);

        // Create a connection to the database
        String serverName = "localhost";
        String mydatabase = "academic";
        String url = "jdbc:mysql://" + serverName + "/" + mydatabase; // a JDBC url
        String username = "root";
        String password = "chukisito";
        System.out.println(password);
            try {
                connection = (Connection) DriverManager.getConnection(url, username, password);
                System.out.println("conecto!");
            } catch (SQLException ex) {
                Logger.getLogger(accesolog.class.getName()).log(Level.SEVERE, null, ex);
            }
        } catch (ClassNotFoundException e) {
        // Could not find the database driver
        }
    }

    public ArrayList<String> getlogin()
    {
        ArrayList<String> logins = new ArrayList<String>();
        Statement st = null;
        ResultSet result = null;
        try {

            st = (Statement) connection.createStatement();
            result = st.executeQuery("select codusuario from usuario;");
            while (result.next()) {
                String a = result.getString("codusuario");
                logins.add(a);
            }

        } catch (SQLException ex) {
            Logger.getLogger(accesolog.class.getName()).log(Level.SEVERE, null, ex);
        }
        return logins;
    }



    public String log(String login, String pas) throws SQLException {

        Statement st = null;
        ResultSet result = null;
        System.out.println(login);
        System.out.println(pas);
        String a;
        st = (Statement) connection.createStatement();
        result = st.executeQuery("select tipo from usuario where codusuario ='"+login+"' and password ='"+pas+"';");
        //String a = result.getString("id");
         //System.out.println(result.getString("id"));
        if(result.next())
        {
            a = result.getString("tipo");
            return a;
        }
        else
            return  a = "error";
        //return result.getString("id");
    }

    
    /*----------------ALUMNO
     * 
     * 
     */
    
    public int verificaCodAlu( String cod ) throws SQLException
    {
        int flag = 1;
        Statement st = null;
        ResultSet result = null;
       
        String a;
        st = (Statement) connection.createStatement();
        result = st.executeQuery("select Codigo from alumno where Codigo ='"+cod+"' ;");
        //String a = result.getString("id");
         //System.out.println(result.getString("id"));
        if(result.next())
        {
            //a = result.getString("Codigo");
            flag = 1;
            
        }
        else
        {
            flag = 0;
        }
           
        
        return flag;
    }

    public void guardarAlumno(String codigo,String nombre, String apellido, String semestre, String telefono, String documento,String email,String direc, String anho) throws SQLException {
        Statement sentencia = null;
        try {
            sentencia = (Statement) connection.createStatement();
            String comando = "INSERT INTO alumno(Cogido,nombre,apellido,semestre,email,telefono,DocumentoId,direccion,anho_ingreso)" +
                    " VALUES ('" + codigo + ",'" + nombre + "','" + apellido + "','" + telefono + "','"+direc+"','" + documento + "','" + email + "','" + direc + "','"+anho+"')";

            sentencia.executeUpdate(comando);
            

        } finally {

        sentencia.close();
      }

    }

   
     public String getnombre(String cod) throws SQLException{//error de acceso a la bd
      String nombre = new String("");
      Statement stmt = null;
      ResultSet rs = null;
      try {
          stmt = (Statement) connection.createStatement();
          //rs = stmt.executeQuery("SELECT COUNT(*) FROM universidad");
          rs = stmt.executeQuery("select nombre from alumno where Codigo='" + cod + "';");
          if (rs.next())
              nombre=(String)(rs.getString(1));

      }
      finally {
        rs.close();
        stmt.close();
      }
      return nombre;
         }

         public String getApellido(String cod) throws SQLException
         {//error de acceso a la bd
      String apellido = new String("");
      Statement stmt = null;
      ResultSet rs = null;
      try {
          stmt = (Statement) connection.createStatement();
          //rs = stmt.executeQuery("SELECT COUNT(*) FROM universidad");
          rs = stmt.executeQuery("select apellido from alumno where Codigo='" + cod + "';");
          if (rs.next())
              apellido=(String)(rs.getString(1));

      }
      finally {
        rs.close();
        stmt.close();
      }
      return apellido;
         }


      public String getSemestre(String cod) throws SQLException
      {//error de acceso a la bd
          String semestre = new String("");
          Statement stmt = null;
          ResultSet rs = null;
          try {
              stmt = (Statement) connection.createStatement();
              //rs = stmt.executeQuery("SELECT COUNT(*) FROM universidad");
              rs = stmt.executeQuery("select semestre from alumno where Codigo='" + cod + "';");
              if (rs.next())
                  semestre=(String)(rs.getString(1));

          } finally {
            rs.close();
            stmt.close();
          }

          return semestre;
  }

       public String getEmail(String cod) throws SQLException
      {//error de acceso a la bd
          String email = new String("");
          Statement stmt = null;
          ResultSet rs = null;
          try {
              stmt = (Statement) connection.createStatement();
              //rs = stmt.executeQuery("SELECT COUNT(*) FROM universidad");
              rs = stmt.executeQuery("select email from alumno where Codigo='" + cod + "';");
              if (rs.next())
                  email=(String)(rs.getString(1));

          } finally {
            rs.close();
            stmt.close();
          }

          return email;
  }

       public String gettelefono(String cod) throws SQLException
      {//error de acceso a la bd
          String telefono = new String("");
          Statement stmt = null;
          ResultSet rs = null;
          try {
              stmt = (Statement) connection.createStatement();
              //rs = stmt.executeQuery("SELECT COUNT(*) FROM universidad");
              rs = stmt.executeQuery("select telefono from alumno where Codigo='" + cod + "';");
              if (rs.next())
                  telefono=(String)(rs.getString(1));

          } finally {
            rs.close();
            stmt.close();
          }

          return telefono;
  }

   public String getDocumentoid(String cod) throws SQLException
      {//error de acceso a la bd
          String documento = new String("");
          Statement stmt = null;
          ResultSet rs = null;
          try {
              stmt = (Statement) connection.createStatement();
              //rs = stmt.executeQuery("SELECT COUNT(*) FROM universidad");
              rs = stmt.executeQuery("select DocumentoId from alumno where Codigo='" + cod + "';");
              if (rs.next())
                  documento=(String)(rs.getString(1));

          } finally {
            rs.close();
            stmt.close();
          }

          return documento;
  }
    
   public String getDireccion (String cod) throws SQLException
      {//error de acceso a la bd
          String direc = new String("");
          Statement stmt = null;
          ResultSet rs = null;
          try {
              stmt = (Statement) connection.createStatement();
              //rs = stmt.executeQuery("SELECT COUNT(*) FROM universidad");
              rs = stmt.executeQuery("select DocumentoId from alumno where Codigo='" + cod + "';");
              if (rs.next())
                  direc =(String)(rs.getString(1));

          } finally {
            rs.close();
            stmt.close();
          }

          return direc;
  }

   public String getanho_ingreso (String cod) throws SQLException
      {//error de acceso a la bd
          String anho = new String("");
          Statement stmt = null;
          ResultSet rs = null;
          try {
              stmt = (Statement) connection.createStatement();
              //rs = stmt.executeQuery("SELECT COUNT(*) FROM universidad");
              rs = stmt.executeQuery("select anho_ingreso from alumno where Codigo='" + cod + "';");
              if (rs.next())
                  anho =(String)(rs.getString(1));

          } finally {
            rs.close();
            stmt.close();
          }

          return anho;
  }

public String getnombre2(String cod) throws SQLException{//error de acceso a la bd
      String nombre = new String("");
      Statement stmt = null;
      ResultSet rs = null;
      try {
          stmt = (Statement) connection.createStatement();
          //rs = stmt.executeQuery("SELECT COUNT(*) FROM universidad");
          rs = stmt.executeQuery("select nombre from alumno where Codigo='" + cod + "';");
          if (rs.next())
              nombre=(String)(rs.getString(1));

      }
      finally {
        rs.close();
        stmt.close();
      }
      return nombre;
         }

   /* public void updateCamposAdmin(String tabla, String cod,ArrayList <String> campos)
    {
        Statement s = (Statement) connection.createStatement();
        String st="UPDATE "+ tabla+ " SET nombre = " + nomb
                            +" WHERE Codigo= '"+cod+"';";
    }*/
    public void updateNombre (String tabla, String cod, String nomb)
    {
        try {
           Statement s = (Statement) connection.createStatement();

           String st="UPDATE "+ tabla+ " SET nombre = " + nomb
                            +" WHERE Codigo= '"+cod+"';";
           //System.out.println(st);
           s.executeUpdate(st);
           } catch (SQLException ex) {//error de acceso a la bd
            Logger.getLogger(accesolog.class.getName()).log(Level.SEVERE, null, ex);
        }

     }

    public void updateApelli (String tabla, String cod, String apell)
    {
        try {
           Statement s = (Statement) connection.createStatement();

           String st="UPDATE "+ tabla+ " SET apellido = " + apell
                            +" WHERE codigo= '"+cod+"';";
           //System.out.println(st);
           s.executeUpdate(st);
           } catch (SQLException ex) {//error de acceso a la bd
            Logger.getLogger(accesolog.class.getName()).log(Level.SEVERE, null, ex);
        }

     }

    public void updateSemes (String tabla, String cod, String sem)
    {
        try {
           Statement s = (Statement) connection.createStatement();

           String st="UPDATE "+ tabla+ " SET semestre = " + sem
                            +" WHERE codigo= '"+cod+"';";
           //System.out.println(st);
           s.executeUpdate(st);
           } catch (SQLException ex) {//error de acceso a la bd
            Logger.getLogger(accesolog.class.getName()).log(Level.SEVERE, null, ex);
        }

     }

    public void updateemail (String tabla, String cod, String ema)
    {
        try {
           Statement s = (Statement) connection.createStatement();

           String st="UPDATE "+ tabla+ " SET email ='" + ema
                            +"' WHERE Codigo= '" +cod+ "';";
           //System.out.println(st);

           s.executeUpdate(st);
           } catch (SQLException ex) {//error de acceso a la bd
            Logger.getLogger(accesolog.class.getName()).log(Level.SEVERE, null, ex);
        }

     }
    public void updatetelef (String tabla, String cod, String tele)
    {
        try {
           Statement s = (Statement) connection.createStatement();

           String st="UPDATE "+ tabla+ " SET telefono = '" + tele
                            +"' WHERE Codigo= '"+cod+"';";
           //System.out.println(st);
           s.executeUpdate(st);
           } catch (SQLException ex) {//error de acceso a la bd
            Logger.getLogger(accesolog.class.getName()).log(Level.SEVERE, null, ex);
        }

     }

    public void updateDoc (String tabla, String cod, String doc)
    {
        try {
           Statement s = (Statement) connection.createStatement();

           String st="UPDATE "+ tabla+ " SET DocumentoId = '" + doc
                            +"' WHERE Codigo= '"+cod+"';";
           //System.out.println(st);
           s.executeUpdate(st);
           } catch (SQLException ex) {//error de acceso a la bd
            Logger.getLogger(accesolog.class.getName()).log(Level.SEVERE, null, ex);
        }

     }

    public void updatedirec (String tabla, String cod, String dir)
    {
        try {
           Statement s = (Statement) connection.createStatement();

           String st="UPDATE "+ tabla+ " SET Direccion = '" + dir
                            +"' WHERE Codigo= '"+cod+"';";
           //System.out.println(st);
           s.executeUpdate(st);
           } catch (SQLException ex) {//error de acceso a la bd
            Logger.getLogger(accesolog.class.getName()).log(Level.SEVERE, null, ex);
        }

     }

    public void updateanho (String tabla, String cod, String anho)
    {
        try {
           Statement s = (Statement) connection.createStatement();

           String st="UPDATE "+ tabla+ " SET anho_ingreso = " + anho
                            +"' WHERE codigo= '"+cod+"';";
           //System.out.println(st);
           s.executeUpdate(st);
           } catch (SQLException ex) {//error de acceso a la bd
            Logger.getLogger(accesolog.class.getName()).log(Level.SEVERE, null, ex);
        }

     }

    ///////////////////////////////////
    /////esto hace el administrador
    ///////////////////////////////////

    public void cambioContrasenha(String cod,String pass ) throws SQLException
    {
        Statement s = (Statement) connection.createStatement();
        s.executeUpdate("UPDATE  usuario set  password ='"+pass+"' WHERE codigo= '"+cod+"';") ;

    }

    public void eliminarUsuario(String cod) throws SQLException
    {

        Statement s = (Statement) connection.createStatement();
        s.executeUpdate("delete from  usuario where codusuario='"+cod+"';");

    }
    public ArrayList<String> getAdministrador(String cod)
    {
            System.out.println(cod);
            ArrayList<String> lista = new ArrayList<String>();
            String dato1 = new String("");
            String dato2 = new String("");
            String dato3 = new String("");
            String dato4 = new String("");
            Statement stmt = null;
            ResultSet rs = null;
             try {
            stmt = (Statement) connection.createStatement();
            //rs = stmt.executeQuery("SELECT COUNT(*) FROM universidad");
            rs = stmt.executeQuery("select * from administrador where Codigo='" + cod + "';");

            if (rs.next()) {
                
                dato1 = (String) (rs.getString(1));
                dato2 = (String) (rs.getString(2));
                dato3 = (String) (rs.getString(3));
                dato4 = (String) (rs.getString(4));
                lista.add(dato1);
                lista.add(dato2);
                lista.add(dato3);
                lista.add(dato4);
            }

        } catch (SQLException ex) {
            Logger.getLogger(accesolog.class.getName()).log(Level.SEVERE, null, ex);
        }
            return lista;
    }
}

